Цель этих упражнений -- дать вам практические навыки использования групповых функций для отбора групп данных. Обращайте внимание на используемые псевдонимы.
Для работы с групповыми функциями синтаксис команды SELECT, определенный в простейшем варианте в третьем шаге, придется расширить добавив фразы GROUP BY и HAVING.
SELECT [DISTINCT {*|столбец [псевдоним], ..... }
FROM {таблица, ....... }
WHERE условие(я)
GROUP BY столбец1, [столбец2, .....]
HAVING критерии_отбора_групп_по_групповым_характеристикам
ORDER BY {столбец|выражение, .... } [ASC|DESC]
Фраза GROUP BY разделяет результаты на подгруппы. Описанные ниже групповые функции вычисляют агрегированные значения по подгруппам. Естественно GROUP BY оперирует только со строками, отобранными по критериям фразы WHERE.
Фраза HAVING отбирает часть групп, часто используя для этого групповые функции.
Вообще HAVING может записываться и до и после фразы GROUP BY, но рекомендуется естественный порядок, приведенный выше.
-- возвращает среднее значение в столбце не учитывая пустых значений; | ||
-- количество строк в группе, в которых выражение имеет непустое значение; | ||
-- максимльное (минимальное) значения выражения в группе; | ||
-- математическое ожидание в группе; | ||
-- сумма значений игнорируя пустые значения; | ||
-- дисперсия в группе; |
Квалификатор DISTINCT заставляет групповую функцию оперировать только с неповторяющимися значениями.
Квалификатор ALL, наоборот, заставляет использовать все значения.
Для всех групповых функций тип данных выражения может быть CHAR, NUMBER или DATE.
Все групповые функции кроме COUNT(*) игнорируют пустые значения.
Предложение WHERE не может использоваться для задания ограничений на групповые функции.
Правило: во фразе SELECT при использовании групповых функций можно записывать только те столбцы, по которым ведется разбиение на группы.
Иначе говоря, любой столбец, стоящий в SELECT не в групповой функции должен стоять и в предложении GROUP BY. Кроме таких столбцов можно записывать литералы и псевдостолбцы.
1. Найдем все отделы, где работает больше трех сотрудников.
SELЕСТ DEPTNO, COUNT(*) FRОМ ЕМР GROUP BY DЕPTNO HAVING COUNT(* ) > 3;
В окне SQL*Plus Вы должны получить следующие значения:
DEPTNO COUNT(*) ------ --------- 20 5 30 6
2. Перечислим самых низкооплачиваемых сотрудников у каждого руководителя. Исключим все группы, где минимальная зарплата меньше 1000. Рассортируем выходные данные по зарплате.
SELЕСТ МGR, MIN(SAL) FRОМ ЕМР GROUP BY MGR HAVING MIN(SAL) >= 1000 ORDER BY MIN(SAL);
В окне SQL*Plus Вы должны получить следующие значения:
MGR MIN(SAL) ----- ---------- 7788 1100 7782 1300 7839 2450 7566 3000 5000